# -*- coding: utf-8 -*-
"""
基于akShare获取行情数据
"""

import akshare as ak
import pandas as pd
import pymysql
from sqlalchemy import create_engine, text, Engine


def get_mysql_engine() -> Engine:
    """
    获取mysql数据库engine
    :return:
    """
    return create_engine('mysql+pymysql://root:123456@127.0.0.1:3308/stock?charset=utf8')


def add_hist_to_db(start_date, end_date, adjust):
    """
    把指定历史行情数据放到数据库

    :param start_date: 开始日期，"20230301"
    :param end_date: 结束日期，"20230320"
    :param adjust: qfq-前复权，hfq-后复权，""-不复权
    :return:
    """
    # 调用实时行情接口，获取所有股票，然后再分别获取每只票的历史行情
    all_stock_df = ak.stock_zh_a_spot_em()[["代码", "名称"]]
    db_columns = [
        "code",
        "name",
        "trade_date",
        "open_price",
        "high_price",
        "low_price",
        "close_price",
        "change_amount",
        "change_percent",
        "amount",
        "turnover_rate"
    ]

    # 表名
    table_name = "stock_daily"
    if adjust != "":
        table_name = table_name + "_" + adjust

    i = 1
    # 股票数量（实时行情df行数）
    count = all_stock_df.shape[0]
    # 多个票合并批量插入，提高整体性能，缩短整体耗时
    batch_df = pd.DataFrame()
    # 数据库engine
    engine = get_mysql_engine()
    for index, row in all_stock_df.iterrows():
        code = row["代码"]
        name = row["名称"]
        print("%s  done......%d/%d" % (name, i, count))
        i = i + 1
        hist_df = ak.stock_zh_a_hist(symbol=code, period='daily', start_date=start_date, end_date=end_date,
                                     adjust=adjust)
        # 有可能没有历史数据，比如今天刚上市的新股
        if hist_df.empty:
            continue
        hist_df["trade_date"] = hist_df["日期"]
        hist_df['open_price'] = hist_df['开盘']
        hist_df['high_price'] = hist_df['最高']
        hist_df['low_price'] = hist_df['最低']
        hist_df['close_price'] = hist_df['收盘']
        hist_df['change_amount'] = hist_df['涨跌额']
        hist_df['change_percent'] = hist_df['涨跌幅']
        hist_df['amount'] = round(hist_df['成交额'] / 100000000, 2)
        hist_df['turnover_rate'] = hist_df['换手率']
        hist_df['code'] = code
        hist_df['name'] = name

        if batch_df.empty:
            batch_df = hist_df
        else:
            batch_df = pd.concat([batch_df, hist_df], axis=0, join='inner')

        # save to mysql
        # 30只票合一批存库
        if count % 30 == 0:
            batch_df[db_columns].to_sql(table_name, engine, index=False, if_exists='append')
            batch_df = pd.DataFrame()

    # 最后剩下的
    if not batch_df.empty:
        batch_df[db_columns].to_sql(table_name, engine, index=False, if_exists='append')
    engine.connect().close()

# add_hist_to_db("20191201", "20191231", "hfq")

